home *** CD-ROM | disk | FTP | other *** search
/ Programming Languages Suite / ProgramD2.iso / Database Designers / Rational Rose 2000 / Rational Setup.EXE / common / odbccat7.sql < prev    next >
Text File  |  1999-08-20  |  16KB  |  636 lines
  1. Rem
  2. Rem  odbccat7.sql
  3. Rem
  4. Rem  $Id: odbccat7.sql,v 1.12 1998/08/28 09:39:15 source Exp $
  5. Rem
  6. Rem  OpenLink ODBC Catalog views for Oracle 7 RDBMS
  7. Rem
  8. Rem  These catalog views provide the ODBC catalog for the
  9. Rem  SQLForeignKeys and SQLPrimaryKeys functions.
  10. Rem  If you plan to use these functions from ODBC, please install
  11. Rem  these views. Refer to your OpenLink database agent documentation
  12. Rem  for further details.
  13. Rem
  14. Rem  (C)Copyright 1993, 1994 OpenLink Software.
  15. Rem  All Rights Reserved.
  16. Rem
  17. Rem  The copyright above and this notice must be preserved in all
  18. Rem  copies of this source code.  The copyright above does not
  19. Rem  evidence any actual or intended publication of this source code.
  20. Rem
  21. Rem  This is unpublished proprietary trade secret source code of
  22. Rem  OpenLink Software.  This source code may not be copied, disclosed,
  23. Rem  distributed, demonstrated or licensed except as expressly 
  24. Rem  authorized by OpenLink Software.
  25. Rem
  26. Rem
  27. Rem NOTICE:
  28. Rem
  29. Rem This script must be run connected to the internal database
  30. Rem in order to register properly. As this might require the DBA
  31. Rem to enter a password this script needs to be run as follows:
  32. Rem 
  33. Rem     log in as the Oracle DBA
  34. Rem     $ sqldba lmode=y
  35. Rem    SQL> connect internal
  36. Rem    enter password when requested by Oracle
  37. Rem    SQL> @odbccat7.sql
  38. Rem     ...........
  39. Rem         <output from running Oracle script deleted>
  40. Rem    ...........
  41. Rem    SQL> quit
  42. Rem
  43. Rem
  44. /
  45. create or replace view OPENLINK$SQL_FOREIGN_KEYS
  46.   (PKTABLE_QUALIFIER, PKTABLE_OWNER, PKTABLE_NAME, PKCOLUMN_NAME,
  47.    FKTABLE_QUALIFIER, FKTABLE_OWNER, FKTABLE_NAME, FKCOLUMN_NAME,
  48.    KEY_SEQ, UPDATE_RULE, DELETE_RULE, FK_NAME, PK_NAME)
  49. as
  50. select NULL, po.name, pb.name, pcl.name,
  51.        NULL, fo.name, fb.name, fcl.name,
  52.        fcc.pos#, 
  53.        1, 
  54.        decode(pcd.refact, 1, 0, 1),
  55.        fco.name, 
  56.        pco.name
  57. from sys.obj$  fb,
  58.      sys.cdef$ fcd,
  59.      sys.ccol$ fcc,
  60.      sys.col$  fcl,
  61.      sys.con$  fco,
  62.      sys.user$ fo,
  63.      sys.obj$  pb,
  64.      sys.cdef$ pcd,
  65.      sys.ccol$ pcc,
  66.      sys.col$  pcl,
  67.      sys.con$  pco,
  68.      sys.user$ po
  69. where fo.user# = fb.owner#
  70.   and fb.type = 2
  71.   and fcd.enabled = 1
  72.   and fcd.obj# = fb.obj#
  73.   and fcc.con# = fcd.con#
  74.   and fco.con# = fcd.con#
  75.   and fcd.type = 4
  76.   and fcl.obj# = fcc.obj#
  77.   and fcl.col# = fcc.col#
  78.   and fcd.rcon# = pcd.con#
  79.   and po.user# = pb.owner#
  80.   and pb.type = 2
  81.   and pcd.obj# = pb.obj#
  82.   and pcc.con# = pcd.con#
  83.   and pco.con# = pcd.con#
  84.   and pcl.obj# = pcc.obj#
  85.   and pcl.col# = pcc.col#
  86.   and pcc.pos# = fcc.pos#
  87.   and (fb.owner# = uid
  88.        or fb.obj# in (select obj#
  89.                       from sys.objauth$
  90.                       where grantee# in (select kzsrorol from x$kzsro))
  91.       )
  92.   and (pb.owner# = uid
  93.        or pb.obj# in (select obj#
  94.                       from sys.objauth$
  95.                       where grantee# in (select kzsrorol from x$kzsro))
  96.       )
  97. /
  98. comment on table OPENLINK$SQL_FOREIGN_KEYS is
  99. 'SQLForeignKeys for the OpenLink ODBC database agent'
  100. /
  101. grant select on OPENLINK$SQL_FOREIGN_KEYS to public with grant option
  102. /
  103. drop public synonym OPENLINK$SQL_FOREIGN_KEYS
  104. /
  105. create public synonym OPENLINK$SQL_FOREIGN_KEYS for OPENLINK$SQL_FOREIGN_KEYS
  106. /
  107. commit
  108. /
  109. create or replace view OPENLINK$SQL_PRIMARY_KEYS
  110.   (TABLE_QUALIFIER, TABLE_OWNER, TABLE_NAME, COLUMN_NAME, POSITION)
  111. as
  112. select NULL, po.name, pb.name, pcl.name, pcc.pos#
  113. from sys.obj$ pb,
  114.      sys.cdef$ pcd,
  115.      sys.ccol$ pcc,
  116.      sys.col$ pcl,
  117.      sys.user$ po
  118. where po.user# = pb.owner#
  119.   and pb.type = 2
  120.   and pcd.type = 2
  121.   and pcd.obj# = pb.obj#
  122.   and pcc.con# = pcd.con#
  123.   and pcl.obj# = pcc.obj#
  124.   and pcl.col# = pcc.col#
  125.   and (pb.owner# = uid
  126.        or pb.obj# in (select obj#
  127.                       from sys.objauth$
  128.                       where grantee# in (select kzsrorol from x$kzsro))
  129.       )
  130. /
  131. comment on table OPENLINK$SQL_PRIMARY_KEYS is
  132. 'SQLPrimaryKeys for the OpenLink ODBC database agent'
  133. /
  134. grant select on OPENLINK$SQL_PRIMARY_KEYS to public with grant option
  135. /
  136. drop public synonym OPENLINK$SQL_PRIMARY_KEYS
  137. /
  138. create public synonym OPENLINK$SQL_PRIMARY_KEYS for OPENLINK$SQL_PRIMARY_KEYS
  139. /
  140. commit
  141. /
  142. create or replace view OPENLINK$SQL_SPEC_COLUMNS
  143.   (SCOPE, COLUMN_NAME, DATA_TYPE, TYPE_NAME, PRECISION, LENGTH, SCALE, 
  144.    INT_ID, INT_COLS, INT_POS, TABLE_OWNER, TABLE_NAME, INDEX_NAME)
  145. as
  146. select 2, c.name, c.type#,
  147.     decode(c.type#, 
  148.     1,  'VARCHAR2',
  149.         2,  decode (c.scale, null, 
  150.         decode(c.precision, null, 'NUMBER', 'FLOAT'),
  151.         'NUMBER'),
  152.         8, 'LONG', 
  153.     9, 'VARCHAR',
  154.         12,'DATE', 
  155.     23,'RAW', 
  156.     24,'LONG RAW',
  157.         69,'ROWID',
  158.     96,'CHAR', 
  159.     105, 'MLSLABEL',
  160.         106, 'MLSLABEL', 
  161.     'UNDEFINED'
  162.     ),
  163.     c.precision, c.length, c.scale, 1, i.cols, ic.pos#, towner.name, t.name,
  164.     i_o.name
  165. from
  166.     sys.obj$ t,
  167.     sys.col$ c,
  168.     sys.ind$ i,
  169.     sys.icol$ ic,
  170.     sys.obj$ i_o,
  171.     sys.user$ towner
  172. where t.type = 2 
  173.   and towner.user# = t.owner#
  174.   and i.bo# = t.obj# 
  175.   and i.unique$ = 1 
  176.   and i_o.obj# = i.obj# 
  177.   and ic.obj# = i_o.obj# 
  178.   and ic.col# = c.col# 
  179.   and c.obj# = t.obj# 
  180.   and (t.owner# = uid
  181.        or t.obj# in (select obj#
  182.                       from sys.objauth$
  183.                       where grantee# in (select kzsrorol from x$kzsro))
  184.       )
  185. union
  186. select 1, 'ROWID', 1, 'CHAR', 18, 18, 0, 2, 1, 1, towner.name, 
  187.   t.name, ''
  188. from
  189.     sys.obj$ t, 
  190.     sys.user$ towner
  191. where t.type = 2 
  192.   and towner.user# = t.owner#
  193.   and (t.owner# = uid
  194.        or t.obj# in (select obj#
  195.                       from sys.objauth$
  196.                       where grantee# in (select kzsrorol from x$kzsro))
  197.       )
  198. /
  199. comment on table OPENLINK$SQL_SPEC_COLUMNS is
  200. 'SQLSpecialColumns for the OpenLink ODBC database agent'
  201. /
  202. grant select on OPENLINK$SQL_SPEC_COLUMNS to public with grant option
  203. /
  204. drop public synonym OPENLINK$SQL_SPEC_COLUMNS
  205. /
  206. create public synonym OPENLINK$SQL_SPEC_COLUMNS for OPENLINK$SQL_SPEC_COLUMNS
  207. /
  208.  
  209. Rem o.type - 9 is packaged objects, 7 is a stored function, 8 is a stored procedure
  210.  
  211. create or replace view OPENLINK$SQL_PROC_COLUMNS
  212.   (PROCEDURE_QUALIFIER,PROCEDURE_OWNER,PROCEDURE_NAME,COLUMN_NAME,
  213.    COLUMN_TYPE,DATA_TYPE,TYPE_NAME,PRECISION,LENGTH,SCALE,RADIX,
  214.    NULLABLE,REMARKS)
  215. as
  216. select NULL, 
  217.     u.name, 
  218.     o.name,
  219.     decode(a.position,
  220.         0,'RETURN_VALUE',
  221.         a.argument),
  222.     decode(a.in_out,
  223.         NULL,1, 
  224.     2,2,
  225.     1,decode(a.position,
  226.           0,5,
  227.           4),
  228.     1),        
  229.     a.type,
  230.     decode(a.type, 
  231.     1,  'VARCHAR2',
  232.         2,  decode (a.scale, 
  233.         null, decode(a.precision, 
  234.                   null, 'NUMBER', 
  235.                   'FLOAT'),
  236.         'NUMBER'),
  237.         8, 'LONG', 
  238.     9, 'VARCHAR',
  239.         12,'DATE', 
  240.     23,'RAW', 
  241.     24,'LONG RAW',
  242.         69,'ROWID',
  243.     96,'CHAR', 
  244.     105, 'MLSLABEL',
  245.         106, 'MLSLABEL', 
  246.     'UNDEFINED'
  247.     ),
  248.     decode(a.precision,
  249.         null,decode(a.type,
  250.                  1,255,
  251.          255),
  252.       a.precision),
  253.     a.length, 
  254.     a.scale, 
  255.     a.radix, 
  256.     2,
  257.     NULL
  258. from
  259.     sys.obj$ o,
  260.     sys.argument$ a,
  261.     sys.user$ u
  262. where
  263.     u.user# = o.owner# and
  264.     a.obj#=o.obj# and
  265.     o.type in (7,8) and 
  266.     o.status=1
  267. UNION
  268. select NULL, 
  269.     o.name, 
  270.     a.procedure$,
  271.     decode(a.position,
  272.         0,'RETURN_VALUE',
  273.         a.argument),
  274.     decode(a.in_out,
  275.         NULL,1, 
  276.     2,2,
  277.     1,decode(a.position,
  278.           0,5,
  279.           4),
  280.     1),        
  281.     a.type,
  282.     decode(a.type, 
  283.     1,  'VARCHAR2',
  284.         2,  decode (a.scale, 
  285.         null, decode(a.precision, 
  286.                   null, 'NUMBER', 
  287.                   'FLOAT'),
  288.         'NUMBER'),
  289.         8, 'LONG', 
  290.     9, 'VARCHAR',
  291.         12,'DATE', 
  292.     23,'RAW', 
  293.     24,'LONG RAW',
  294.         69,'ROWID',
  295.     96,'CHAR', 
  296.     105, 'MLSLABEL',
  297.         106, 'MLSLABEL', 
  298.     'UNDEFINED'
  299.     ),
  300.     decode(a.precision,
  301.         null,decode(a.type,
  302.                  1,255,
  303.          255),
  304.       a.precision),
  305.     a.length, 
  306.     a.scale, 
  307.     a.radix, 
  308.     2,
  309.     NULL
  310. from
  311.     sys.obj$ o,
  312.     sys.argument$ a,
  313.     sys.user$ u
  314. where
  315.     u.user# = o.owner# and
  316.     a.obj#=o.obj# and
  317.     o.type = 9 and 
  318.     o.status=1
  319. /
  320. comment on table OPENLINK$SQL_PROC_COLUMNS is
  321. 'SQLProcedureColumns for the OpenLink ODBC database agent'
  322. /
  323. grant select on OPENLINK$SQL_PROC_COLUMNS to public with grant option
  324. /
  325. drop public synonym OPENLINK$SQL_PROC_COLUMNS
  326. /
  327. create public synonym OPENLINK$SQL_PROC_COLUMNS for OPENLINK$SQL_PROC_COLUMNS
  328. /
  329. commit
  330. /
  331.  
  332. Rem
  333. Rem  STORED FUNCTIONS FOR SCALAR FUNCTION SUPPORT
  334. Rem
  335. drop package OPENLINK;
  336. /
  337.  
  338. create package OPENLINK as
  339.   function INSERT_STRING(stra in string, strt in number, leng in number, strb in string) return string;
  340.   pragma restrict_references(INSERT_STRING,wnds,wnps);
  341.   function LEFT(stra in string, cnt in number) return string;
  342.   pragma restrict_references(LEFT,wnds,wnps);
  343.   function RIGHT(stra in string, cnt in number) return string;
  344.   pragma restrict_references(RIGHT,wnds,wnps);
  345.   function LOCATE(stra in string, strb in string, strt in number default 1) return number;
  346.   pragma restrict_references(LOCATE,wnds,wnps);
  347.   function REPEAT(strin in string, cnt in number) return string;
  348.   pragma restrict_references(REPEAT,wnds,wnps);
  349.   function RADIANS(degrees in float) return float; 
  350.   pragma restrict_references(RADIANS,wnds,wnps);
  351.   function DEGREES(radians in float) return float;
  352.   pragma restrict_references(DEGREES,wnds,wnps);
  353.   function COT(radians in float) return float;
  354.   pragma restrict_references(COT,wnds,wnps);
  355.   function COUNT_IN_PARMS(obj_id in number) return number;
  356.   pragma restrict_references(COUNT_IN_PARMS,wnds,wnps);
  357.   function COUNT_OUT_PARMS(obj_id in number) return number;
  358.   pragma restrict_references(COUNT_OUT_PARMS,wnds,wnps);
  359.   function PCOUNT_IN_PARMS(obj_id in number, proc in string) return number;
  360.   pragma restrict_references(PCOUNT_IN_PARMS,wnds,wnps);
  361.   function PCOUNT_OUT_PARMS(obj_id in number, proc in string) return number;
  362.   pragma restrict_references(PCOUNT_OUT_PARMS,wnds,wnps);
  363.   function PACK_OBJ_TYPE(obj_id in number, proc in string) return number;
  364.   pragma restrict_references(PACK_OBJ_TYPE,wnds,wnps);
  365. end;
  366. /
  367.  
  368. create package body OPENLINK as
  369.  
  370.   function INSERT_STRING(stra in string, strt in number, leng in number, strb in string)
  371.   return string as
  372.   begin
  373.     return SUBSTR(stra,1,strt-1) || strb || SUBSTR(stra,strt+leng);
  374.   end;
  375.  
  376.   function LEFT(stra in string, cnt in number)
  377.   return string as
  378.   begin
  379.     return SUBSTR(stra,1,cnt);
  380.   end;
  381.  
  382.   function RIGHT(stra in string, cnt in number)
  383.   return string as
  384.   begin
  385.     return SUBSTR(stra,(LENGTH(stra)-cnt)+1);
  386.   end;
  387.  
  388.   function LOCATE(stra in string, strb in string, strt in number default 1)
  389.   return number as
  390.   begin
  391.     return INSTR(strb,stra,strt);
  392.   end;
  393.  
  394.   function REPEAT(strin in string, cnt in number)
  395.   return string as
  396.   begin
  397.     declare 
  398.       tally number;
  399.       outstring string(2000);
  400.     begin
  401.       tally := cnt;
  402.       WHILE tally!=0 LOOP
  403.         outstring := outstring || strin;
  404.         tally := tally-1;
  405.       END LOOP;
  406.       return outstring;
  407.     end;
  408.   end;
  409.  
  410.   function RADIANS(degrees in float) 
  411.   return float as
  412.   begin
  413.     return (degrees/360)*2*3.1415926545;
  414.   end;
  415.  
  416.   function DEGREES(radians in float)
  417.   return float as
  418.   begin
  419.     return (radians/(2*3.141592654))*360;
  420.   end;
  421.   
  422.   function COT(radians in float)
  423.   return float as
  424.   begin
  425.     return (1/tan(radians));
  426.   end;
  427.  
  428.   function COUNT_IN_PARMS(obj_id in number)
  429.   return number as
  430.     nparms NUMBER;
  431.   begin
  432.     select count(*) into nparms 
  433.       from   argument$ a
  434.       where  a.obj#=obj_id
  435.         and  in_out is null;
  436.     if nparms is null then
  437.       nparms:=0;
  438.     end if;
  439.     return nparms;
  440.   end;
  441.  
  442.   function COUNT_OUT_PARMS(obj_id in number)
  443.   return number as
  444.     nparms NUMBER;
  445.   begin
  446.     select count(*) into nparms 
  447.       from   argument$ a
  448.       where  a.obj#=obj_id
  449.         and  in_out=1;
  450.     if nparms is null then
  451.       nparms:=0;
  452.     end if;
  453.     return nparms;
  454.   end;
  455.  
  456.   function PCOUNT_IN_PARMS(obj_id in number, proc in string)
  457.   return number as
  458.     nparms NUMBER;
  459.   begin
  460.     select count(*) into nparms 
  461.       from   argument$ a, obj$ o
  462.       where  a.obj#=obj_id
  463.         and  a.obj#=o.obj#
  464.     and  o.type=9
  465.         and  a.procedure$=proc
  466.         and  in_out is null;
  467.     if nparms is null then
  468.       nparms:=0;
  469.     end if;
  470.     return nparms;
  471.   end;
  472.  
  473.   function PCOUNT_OUT_PARMS(obj_id in number, proc in string)
  474.   return number as
  475.     nparms NUMBER;
  476.   begin
  477.     select count(*) into nparms 
  478.       from   argument$ a, obj$ o
  479.       where  a.obj#=obj_id
  480.         and  a.obj#=o.obj#
  481.     and  o.type=9
  482.         and  a.procedure$=proc
  483.         and  in_out=1;
  484.     if nparms is null then
  485.       nparms:=0;
  486.     end if;
  487.     return nparms;
  488.   end;
  489.  
  490.   function PACK_OBJ_TYPE(obj_id in number, proc in string)
  491.   return number as
  492.     objtype NUMBER;
  493.   begin
  494.     begin
  495.       select a.position into objtype 
  496.         from   argument$ a, obj$ o
  497.           where  a.obj#=obj_id
  498.             and  a.procedure$=proc
  499.             and  o.obj#=a.obj#
  500.         and  o.type=9
  501.             and  a.position=0
  502.             and  a.in_out=1;
  503.     exception
  504.       when too_many_rows then
  505.         objtype:=1;
  506.     end;
  507.     if objtype=0 then
  508.       objtype:=2;
  509.     else
  510.       objtype:=1;
  511.     end if;
  512.     return objtype;
  513.   end;
  514.  
  515. end openlink;
  516. /
  517. show errors;
  518. /
  519. drop public synonym oplora7;
  520. /
  521. create public synonym oplora7 for sys.openlink;
  522. /
  523. grant execute on sys.openlink to public with grant option;
  524. /
  525. alter package openlink COMPILE;
  526. /
  527.  
  528. Rem 
  529. Rem  Note: Function type for Packaged Objects not supported as yet.
  530. Rem  Awaiting feedback from Oracle Corp on outstanding issue.
  531. Rem
  532. create or replace view OPENLINK$SQL_PROCEDURES
  533.   (PROCEDURE_QUALIFIER,PROCEDURE_OWNER,PROCEDURE_NAME,NUM_INPUT_PARAMS,
  534.    NUM_OUTPUT_PARAMS,NUM_RESULT_SETS,REMARKS,PROCEDURE_TYPE)
  535. as
  536. select NULL,
  537.     u.name,
  538.     o.name,
  539.     openlink.count_in_parms(o.obj#),
  540.     openlink.count_out_parms(o.obj#),
  541.     -1,
  542.     NULL,
  543.     decode(o.type,7,1,8,2,0)
  544. from
  545.     sys.obj$ o,
  546.     sys.user$ u
  547. where
  548.     u.user#=o.owner# and
  549.     o.type in (7,8) and
  550.     o.status=1
  551. union
  552. select NULL,
  553.     o1.name,
  554.     a1.procedure$,
  555.     openlink.pcount_in_parms(o1.obj#,a1.procedure$),
  556.     openlink.pcount_out_parms(o1.obj#,a1.procedure$),
  557.     -1,
  558.     NULL,
  559.     0
  560. from
  561.     obj$ o1,
  562.     argument$ a1
  563. where
  564.     a1.obj#=o1.obj# and
  565.     o1.type=9 and
  566.     o1.status=1
  567. /
  568. comment on table OPENLINK$SQL_PROCEDURES is
  569. 'SQLProcedures for the OpenLink ODBC database agent'
  570. /
  571. grant select on OPENLINK$SQL_PROCEDURES to public with grant option
  572. /
  573. drop public synonym OPENLINK$SQL_PROCEDURES
  574. /
  575. create public synonym OPENLINK$SQL_PROCEDURES for OPENLINK$SQL_PROCEDURES
  576. /
  577.  
  578. commit
  579. /
  580.  
  581.  
  582. Rem
  583. Rem  Note: Function type for Packaged Objects not supported as yet.
  584. Rem  Awaiting feedback from Oracle Corp on outstanding issue.
  585. Rem
  586. create or replace view OPENLINK$SQL_PROCS_NOCOUNT
  587.   (PROCEDURE_QUALIFIER,PROCEDURE_OWNER,PROCEDURE_NAME,NUM_INPUT_PARAMS,
  588.    NUM_OUTPUT_PARAMS,NUM_RESULT_SETS,REMARKS,PROCEDURE_TYPE)
  589. as
  590. select NULL,
  591.     u.name,
  592.     o.name,
  593.     -1,
  594.     -1,
  595.     -1,
  596.     NULL,
  597.     decode(o.type,7,1,8,2,0)
  598. from
  599.     sys.obj$ o,
  600.     sys.user$ u
  601. where
  602.     u.user#=o.owner# and
  603.     o.type in (7,8) and
  604.     o.status=1
  605. union
  606. select NULL,
  607.     o1.name,
  608.     a1.procedure$,
  609.     -1,
  610.     -1,
  611.     -1,
  612.     NULL,
  613.     0
  614. from
  615.     obj$ o1,
  616.     argument$ a1
  617. where
  618.     a1.obj#=o1.obj# and
  619.     o1.type=9 and
  620.     o1.status=1
  621. /
  622. comment on table OPENLINK$SQL_PROCS_NOCOUNT is
  623. 'SQLProcedures for the OpenLink ODBC database agent with no support for counting params'
  624. /
  625. grant select on OPENLINK$SQL_PROCS_NOCOUNT to public with grant option
  626. /
  627. drop public synonym OPENLINK$SQL_PROCS_NOCOUNT
  628. /
  629. create public synonym OPENLINK$SQL_PROCS_NOCOUNT for OPENLINK$SQL_PROCS_NOCOUNT
  630. /
  631.  
  632. commit
  633. /
  634.  
  635.  
  636.